﻿Option Explicit On
Imports System.Data.OleDb
Imports Microsoft.Office.Interop.Access

Public Class E1B_データ前処理F

    Private Sub E1B_データ前処理F_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        著作L.Text = 著作
        フォルダー確認()
    End Sub
    Private Sub テーブルアタッチBt_Click(sender As Object, e As EventArgs) Handles テーブルアタッチBt.Click

        メモ1Tb.Text = "Access新規作成..." : メモ1Tb.BackColor = Color.LightYellow : Me.Refresh()
        Db新規作成(MyDB)
        ImportExcelToAccess(MyDB, "T100_Data", ShippingExcel, ShippingSheet)
        メモ1Tb.Text = "Db新規作成 出荷データインポート 完了" : メモ1Tb.BackColor = Color.LightYellow : Me.Refresh()

        ' 一括処理用のテーブルリスト
        Dim tables As String() = {
            "My_DATA11",
            "T110_区分換算",
            "T150_B単位出荷E集計",
            "T150_B単位出荷I集計",
            "T150_C単位出荷E集計",
            "T150_C単位出荷I集計",
            "T160_全出荷E集計",
            "T160_全出荷I集計",
            "T180_EIQランク",
            "T200"
        }

        ' 単一の Access インスタンスで TransferDatabase を実行（高速化）
        テーブルアタッチ複数(tables)

        ' テーブルアタッチ後、T150/T160 系テーブルから「キー=0」の行を削除して不要行を除外
        Try
            Dim targetPath As String = IO.Path.Combine(Tera計算DataPath, "EIQ分析AI化", "EIQ_AIモデル.accdb")
            Using conn As New OleDbConnection($"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={targetPath};")
                conn.Open()
                For Each t As String In tables
                    Try
                        Using cmd As New OleDbCommand($"DELETE FROM [{t}] WHERE [キー]=0", conn)
                            cmd.ExecuteNonQuery()
                        End Using
                    Catch
                        ' テーブルがなかったり列が無い場合は無視して続行
                    End Try
                Next
                conn.Close()
            End Using
        Catch
            ' 削除処理で問題があっても後続処理に影響させない
        End Try

        メモ2Tb.Text = "テーブルアタッチ 完了" : メモ2Tb.BackColor = Color.LightYellow : Me.Refresh()

        メモ3Tb.Text = "乱数追加（6テーブル）を開始します..."

        乱数追加()
        メモ3Tb.Text = "乱数追加（6テーブル）完了" : メモ3Tb.BackColor = Color.LightYellow : Me.Refresh()
        T150_T160に判定を追加()
        メモ4Tb.Text = "T150_T160に判定を追加 完了" ： メモ4Tb.BackColor = Color.LightYellow : Me.Refresh()

        メモ5Tb.Text = "E1B_データ前処理完了、 閉じて,メニューに戻る" ： メモ5Tb.BackColor = Color.PaleTurquoise : Me.Refresh()


    End Sub
    Private Sub E1B_閉じるTb_Click(sender As Object, e As EventArgs) Handles E1B_閉じるTb.Click
        Me.Close()
    End Sub

    Private Sub テーブルアタッチ複数(tables As IEnumerable(Of String))
        Dim sourceDbPath As String = Tera計算DataPath & "\Tera計算.accdb"
        Dim targetDbPath As String = Tera計算DataPath & "\EIQ分析AI化\EIQ_AIモデル.accdb"

        Dim accessApp As Application = Nothing
        Try
            accessApp = New Application()
            accessApp.OpenCurrentDatabase(targetDbPath)

            For Each tableName As String In tables
                Try
                    ' 既存テーブルがあれば削除
                    Try
                        accessApp.DoCmd.DeleteObject(AcObjectType.acTable, tableName)
                    Catch
                        ' 存在しない場合は無視
                    End Try

                    ' TransferDatabase でインポート（ソースからターゲットへコピー）
                    accessApp.DoCmd.TransferDatabase(
                        AcDataTransferType.acImport,
                        "Microsoft Access",
                        sourceDbPath,
                        AcObjectType.acTable,
                        tableName,
                        tableName
                    )



                Catch ex As Exception
                    ' TransferDatabase が失敗した場合は既存のフォールバックを使う

                    Try
                        テーブルアタッチ(tableName)
                    Catch ex2 As Exception

                    End Try
                End Try
            Next

        Catch ex As Exception
            MessageBox.Show("Access 一括アタッチエラー: " & ex.Message, "エラー", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If accessApp IsNot Nothing Then
                Try
                    accessApp.CloseCurrentDatabase()
                    accessApp.Quit()
                Catch
                End Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(accessApp)
            End If
        End Try
    End Sub
    Private Sub テーブルアタッチ(Mytebl As String)
        ' --- パス設定 ---
        Dim sourcePath As String = IO.Path.Combine(Tera計算DataPath, "Tera計算.accdb")
        Dim targetPath As String = IO.Path.Combine(Tera計算DataPath, "EIQ分析AI化", "EIQ_AIモデル.accdb")
        Dim tableName As String = Mytebl

        If Not IO.File.Exists(sourcePath) Then
            MessageBox.Show("ソースDBが見つかりません: " & sourcePath, "エラー", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
        End If
        If Not IO.File.Exists(targetPath) Then
            MessageBox.Show("ターゲットDBが見つかりません: " & targetPath, "エラー", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
        End If

        ' 接続文字列（インポート先をベースにする）
        Dim connString As String = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={targetPath};"

        Using conn As New OleDbConnection(connString)
            Try
                conn.Open()

                ' 1. 既存テーブルの削除（存在する場合のみ）
                Try
                    Using dropCmd As New OleDbCommand($"DROP TABLE [{tableName}]", conn)
                        dropCmd.ExecuteNonQuery()
                    End Using
                Catch
                    ' テーブルがない場合は無視して続行
                End Try

                ' 2. まず Access の TransferDatabase を試行（高速で安定する場合がある）
                Dim transferSucceeded As Boolean = False
                Try
                    テーブルアタッチ1(tableName)
                    transferSucceeded = True
                Catch ex As Exception

                End Try

                If Not transferSucceeded Then
                    ' 3. フォールバック: 高速インポートSQL
                    Dim sql As String = $"SELECT * INTO [{tableName}] FROM [{tableName}] IN '{sourcePath}'"
                    Using cmd As New OleDbCommand(sql, conn)
                        cmd.ExecuteNonQuery()
                    End Using
                End If

            Catch ex As Exception
                ' どちらの手法でも失敗した場合はユーザーに通知
                MessageBox.Show("テーブルコピー失敗: " & ex.Message, "エラー", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using
    End Sub
    Private Sub テーブルアタッチ1(Mytebl As String)
        ' パスの設定
        Dim sourceDbPath As String = Tera計算DataPath & "\Tera計算.accdb"
        Dim targetDbPath As String = Tera計算DataPath & "\EIQ分析AI化\EIQ_AIモデル.accdb"
        Dim tableName As String = Mytebl
        ' Accessアプリケーションオブジェクトの作成
        Dim accessApp As New Application()

        Try
            ' インポート先のデータベースを開く
            accessApp.OpenCurrentDatabase(targetDbPath)

            ' データベース間インポートの実行
            ' 引数: インポート, 種類, ソースDBパス, オブジェクト種類, ソーステーブル名, ターゲット名
            accessApp.DoCmd.TransferDatabase(
                AcDataTransferType.acImport,
                "Microsoft Access",
                sourceDbPath,
                AcObjectType.acTable,
                tableName,
                tableName
            )

            'MessageBox.Show("インポートが完了しました。", "成功")

        Catch ex As Exception
            MessageBox.Show("エラーが発生しました: " & ex.Message, "エラー")
        Finally
            ' データベースを閉じてリソースを解放
            accessApp.CloseCurrentDatabase()
            accessApp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(accessApp)
        End Try
    End Sub

    Private Sub 乱数追加()
        Dim ss As Integer = 0
        Dim tables As String() = {
            "T150_B単位出荷E集計",
            "T150_B単位出荷I集計",
            "T150_C単位出荷E集計",
            "T150_C単位出荷I集計",
            "T160_全出荷E集計",
            "T160_全出荷I集計"
        }

        If String.IsNullOrWhiteSpace(MyDB) Then
            MsgBox("DBパスが未設定のため乱数追加を中止します。")
            Return
        End If

        Try
            ' ADOXでテーブル情報を取得（リンク判定のため）
            Dim catalog As New ADOX.Catalog()
            catalog.let_ActiveConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";")

            ' --- 1) まず各テーブルに「乱数」列がなければ追加（可能な限り） ---
            For Each t As String In tables
                If Not Search_Table(t) Then Continue For
                ss += 1
                Try
                    Dim isLinked As Boolean = False
                    Dim remoteDatasource As String = MyDB
                    Dim remoteTableName As String = t

                    For i As Integer = 0 To catalog.Tables.Count - 1
                        If String.Equals(catalog.Tables(i).Name, t, StringComparison.OrdinalIgnoreCase) Then
                            Try
                                Dim props = catalog.Tables(i).Properties
                                If props.Item("Jet OLEDB:Create Link") IsNot Nothing AndAlso
                                   CBool(props.Item("Jet OLEDB:Create Link").Value) = True Then
                                    isLinked = True
                                    Try
                                        remoteDatasource = props.Item("Jet OLEDB:Link Datasource").Value.ToString()
                                    Catch
                                    End Try
                                    Try
                                        remoteTableName = props.Item("Jet OLEDB:Remote Table Name").Value.ToString()
                                    Catch
                                    End Try
                                End If
                            Catch
                                ' プロパティ参照失敗は無視
                            End Try
                            Exit For
                        End If
                    Next

                    Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & remoteDatasource & ";")
                        cn.Open()

                        ' スキーマ取得で列存在を確認
                        Dim colExists As Boolean = False
                        Using schemaCmd As New OleDbCommand("SELECT * FROM [" & remoteTableName & "] WHERE 1=0", cn)
                            Using rdr As OleDbDataReader = schemaCmd.ExecuteReader(CommandBehavior.SchemaOnly)
                                Dim dtSchema As DataTable = rdr.GetSchemaTable()
                                If dtSchema IsNot Nothing Then
                                    For Each row As DataRow In dtSchema.Rows
                                        If String.Equals(row("ColumnName").ToString(), "乱数", StringComparison.OrdinalIgnoreCase) Then
                                            colExists = True
                                            Exit For
                                        End If
                                    Next
                                End If
                            End Using
                        End Using

                        If Not colExists Then
                            Try
                                Dim addCmd As New OleDbCommand("ALTER TABLE [" & remoteTableName & "] ADD COLUMN 乱数 DOUBLE", cn)
                                addCmd.ExecuteNonQuery()
                            Catch ex As Exception
                                ' 次のテーブルへ


                                Continue For
                            End Try
                        End If

                        cn.Close()
                    End Using
                Catch ex As Exception

                End Try
            Next

            ' --- 2) 乱数割当（順位が無ければ 出荷先/アイテム を使って更新） ---
            ' 再現性のある乱数列にするため固定シードを利用
            ' 必要なら My_DATA1 の 分析日 や MyDB パスからシードを算出するよう変更可能
            Dim seed As Integer = 123456
            Dim rnd As New Random(seed) ' 固定シード（毎回同じ乱数列）

            For Each t As String In tables
                If Not Search_Table(t) Then Continue For

                ' 再度 ADOX でリンク先を決定
                Dim catalog2 As New ADOX.Catalog()
                catalog2.let_ActiveConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MyDB & ";")
                Dim remoteDatasource As String = MyDB
                Dim remoteTableName As String = t

                For i As Integer = 0 To catalog2.Tables.Count - 1
                    If String.Equals(catalog2.Tables(i).Name, t, StringComparison.OrdinalIgnoreCase) Then
                        Try
                            Dim props = catalog2.Tables(i).Properties
                            If props.Item("Jet OLEDB:Create Link") IsNot Nothing AndAlso
                               CBool(props.Item("Jet OLEDB:Create Link").Value) = True Then
                                Try
                                    remoteDatasource = props.Item("Jet OLEDB:Link Datasource").Value.ToString()
                                Catch
                                End Try
                                Try
                                    remoteTableName = props.Item("Jet OLEDB:Remote Table Name").Value.ToString()
                                Catch
                                End Try
                            End If
                        Catch
                        End Try
                        Exit For
                    End If
                Next

                Try
                    Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & remoteDatasource & ";")
                        cn.Open()

                        ' スキーマで利用可能なキー列を判定（順位、出荷先、アイテムの順）
                        Dim hasJuni As Boolean = False, hasDest As Boolean = False, hasItem As Boolean = False
                        Using schemaCmd As New OleDbCommand("SELECT * FROM [" & remoteTableName & "] WHERE 1=0", cn)
                            Using rdr As OleDbDataReader = schemaCmd.ExecuteReader(CommandBehavior.SchemaOnly)
                                Dim dtSchema As DataTable = rdr.GetSchemaTable()
                                If dtSchema IsNot Nothing Then
                                    For Each row As DataRow In dtSchema.Rows
                                        Dim cname = row("ColumnName").ToString()
                                        If String.Equals(cname, "順位", StringComparison.OrdinalIgnoreCase) Then hasJuni = True
                                        If String.Equals(cname, "出荷先", StringComparison.OrdinalIgnoreCase) Then hasDest = True
                                        If String.Equals(cname, "アイテム", StringComparison.OrdinalIgnoreCase) Then hasItem = True
                                    Next
                                End If
                            End Using
                        End Using

                        ' 更新に使う列がない場合はスキップ
                        If Not (hasJuni Or hasDest Or hasItem) Then
                            Continue For
                        End If

                        ' 読み取り用 SQL を組立て
                        Dim selectCols As New List(Of String)
                        If hasJuni Then selectCols.Add("[順位]")
                        If hasDest Then selectCols.Add("[出荷先]")
                        If hasItem Then selectCols.Add("[アイテム]")
                        Dim selSql As String = "SELECT " & String.Join(", ", selectCols.ToArray()) & " FROM [" & remoteTableName & "]"

                        Using selCmd As New OleDbCommand(selSql, cn)
                            Using rdr As OleDbDataReader = selCmd.ExecuteReader()
                                If Not rdr.HasRows Then
                                    Continue For
                                End If

                                ' 更新コマンドを用意（各キー別）
                                Dim updJ As New OleDbCommand("UPDATE [" & remoteTableName & "] SET 乱数 = ? WHERE [順位] = ?", cn)
                                updJ.Parameters.Add(New OleDbParameter("?", OleDbType.Double))
                                updJ.Parameters.Add(New OleDbParameter("?", OleDbType.Integer))

                                Dim updDest As New OleDbCommand("UPDATE [" & remoteTableName & "] SET 乱数 = ? WHERE [出荷先] = ?", cn)
                                updDest.Parameters.Add(New OleDbParameter("?", OleDbType.Double))
                                updDest.Parameters.Add(New OleDbParameter("?", OleDbType.VarChar, 255))

                                Dim updItem As New OleDbCommand("UPDATE [" & remoteTableName & "] SET 乱数 = ? WHERE [アイテム] = ?", cn)
                                updItem.Parameters.Add(New OleDbParameter("?", OleDbType.Double))
                                updItem.Parameters.Add(New OleDbParameter("?", OleDbType.VarChar, 255))

                                Dim trans As OleDb.OleDbTransaction = cn.BeginTransaction()
                                updJ.Transaction = trans
                                updDest.Transaction = trans
                                updItem.Transaction = trans

                                Try
                                    While rdr.Read()
                                        Dim val As Double = rnd.NextDouble()
                                        If hasJuni AndAlso Not IsDBNull(rdr("順位")) Then
                                            Try
                                                updJ.Parameters(0).Value = val
                                                updJ.Parameters(1).Value = Convert.ToInt32(rdr("順位"))
                                                updJ.ExecuteNonQuery()
                                            Catch
                                                ' 個別更新失敗は行を飛ばす
                                            End Try
                                        ElseIf hasDest AndAlso Not IsDBNull(rdr("出荷先")) Then
                                            Try
                                                updDest.Parameters(0).Value = val
                                                updDest.Parameters(1).Value = rdr("出荷先").ToString()
                                                updDest.ExecuteNonQuery()
                                            Catch
                                            End Try
                                        ElseIf hasItem AndAlso Not IsDBNull(rdr("アイテム")) Then
                                            Try
                                                updItem.Parameters(0).Value = val
                                                updItem.Parameters(1).Value = rdr("アイテム").ToString()
                                                updItem.ExecuteNonQuery()
                                            Catch
                                            End Try
                                        End If
                                    End While

                                    trans.Commit()
                                Catch ex As Exception
                                    trans.Rollback()
                                    Throw
                                Finally
                                    updJ.Dispose()
                                    updDest.Dispose()
                                    updItem.Dispose()
                                End Try
                            End Using
                        End Using

                        cn.Close()
                    End Using
                Catch ex As Exception

                End Try
            Next


        Catch ex As Exception

        End Try
    End Sub


End Class